<?php

class Post {
	public static function new_post($post) {
		if (!isset($_SESSION['user']))
			return 0; // user is not login
		$title = $post['title'];
		$d_nid = (int)$post['default_nid'];
		$nids = explode(',', DB::esc($post['nids']));
		$body = nl2br($post['body']);
		$uid = $_SESSION['user']['uid']; // only enable posting by current user

		$query = "INSERT INTO post (title, body, uid, default_nid) VALUES ('" .
				DB::esc($title) . "', '" .
				DB::esc($body) . "', '$uid', '$d_nid')";
		if (DB::query($query)) { // posting is successful
			$pid = DB::getMySQLiObject()->insert_id;
			// update the post_ts
			$ts = DB::query("SELECT ts FROM post WHERE pid = '$pid'")->fetch_object()->ts;
			DB::query("UPDATE post SET post_ts = '$ts' WHERE pid = '$pid'");

			foreach ($nids as $nid) {
				DB::query("INSERT INTO post_nebula (uid, nid, pid) VALUES ('$uid', '$nid', '$pid')");
			}
			
			return $pid; // posting is done
		} else {
			return -3; // something is wrong
		}
	}

	// note: this method should never be used in actual application because it is too slow
	public static function show_posts($max_num) {
		// get a maximun $max_num posts
		$max_num = DB::esc($max_num);
		$posts = array();
		$query = "SELECT GROUP_CONCAT( nid ) AS nids, GROUP_CONCAT( name ) AS names, GROUP_CONCAT( icon ) AS icons,
					post.ts AS ts, username, user.uid AS uid, avatar, pid, title, body, IFNULL(rating,0) AS rating, rater, toshow, default_nid
					FROM post
					LEFT JOIN (
					SELECT SUM( score ) AS rating, COUNT( uid ) AS rater, pid
					FROM post_score
					GROUP BY pid
					) score_table
					USING ( pid ) 
					JOIN user
					USING ( uid ) 
					JOIN post_nebula
					USING ( pid ) 
					JOIN nebula
					USING ( nid )
					WHERE toshow = 'true' 
					GROUP BY pid
					ORDER BY rating DESC
					LIMIT 0, $max_num
					";
		$post_list = DB::query($query);
		$mg_flag = get_magic_quotes_gpc();
		while ($post = $post_list->fetch_array()) {
			$names = explode(',', $post['names']);
			$nids  = explode(',', $post['nids']);
			$icons = explode(',', $post['icons']);
			$post['nebulas'] = Array();
			$post["score"] = Array( "score"=> $post['rating'], "count"=> $post['rater'] );
			for( $i = 0; $i < count( $names ); $i ++ ) {
				$post['nebulas'][] = Array( 'name' => $names[$i], 'nid' => $nids[$i], 'icon' => $icons[$i] );
			}
			$post['author'] = Array( "username"=>$post['username'], "avatar"=>$post['avatar'] );
			if( $post['rater'] == null ) {
				$post["score"] = Array( "score"=> 0, "count"=> 0 );
			} else {
				$post["score"] = Array( "score"=> $post['rating'], "count"=> $post['rater'] );
			}
			if( $mg_flag ) {
				$post['title']  = stripslashes( $post['title']  );
				$post['body']  = stripslashes( $post['body']  );
			}
			$posts[] = $post;
		}
		return $posts;
	}

	public static function rate_post($pid, $rate) {
		$uid = $_SESSION['user']['uid'];
		$pid = DB::esc($pid);
		$rate = DB::esc($rate);
		if ($rate != "-1" && $rate != "1") {
			return -1;
		}

		if (Post::rated_by_uid($pid, $uid) == 0) {
			// this post has not been rated by the current user
			$query = "INSERT INTO post_score (pid, uid, score) VALUES ('$pid', '$uid', '$rate')";
            if (DB::query($query)) {
                return 1;
            } else {
                return 0;
            }
		} else {
			//$query = "UPDATE post_score SET score = '$rate' WHERE pid = '$pid' AND uid = '$uid'";
            return 2;
		}
	}

	// returns -1, 0(rated_by_uidunrated), 1
	public static function rated_by_uid($pid, $uid) {
		$pid = DB::esc($pid);
		$uid = DB::esc($uid);
		
		$query = "SELECT score FROM post_score WHERE pid = '$pid' AND uid = '$uid'";
		$res = DB::query($query);
		if( $res->num_rows ){
			return $res->fetch_object()->score;
		} else {
			return 0;
		}
	}
	
	public static function total_score($pid) {
		$pid = DB::esc($pid);
		$query = "SELECT SUM(score) AS score, COUNT(uid) AS count FROM post_score WHERE pid = '$pid' GROUP BY pid";
		$res = DB::query($query);
		if( $res->num_rows ) {
			return $res->fetch_object();
		} else {
			return 'null'; // not rated yet
		}
	}
	

	public static function get_post_content($pid) {
		$pid = DB::esc($pid);
		$uid = $_SESSION['user']['uid'];
                $result = DB::query("SELECT title,post.pid,GROUP_CONCAT( nid ) AS nids, GROUP_CONCAT( name ) AS nnames,
                            post.uid AS uid,body, post.ts AS ts, IFNULL(post_lit,0) AS post_lit,IFNULL(post_dim,0) AS post_dim,
                            GROUP_CONCAT(icon) AS nicons, mp.mpid IS NOT NULL AS marked, post_rated.pid IS NOT NULL AS post_rated
								FROM post
								JOIN post_nebula
								USING ( pid ) 
								JOIN nebula
								USING ( nid )
                                                                LEFT JOIN (SELECT pid,SUM(score) AS post_lit FROM post_score WHERE score=1 GROUP BY pid)post_lit_sum USING(pid)
                                                                LEFT JOIN (SELECT pid,SUM(score) AS post_dim FROM post_score WHERE score=-1 GROUP BY pid)post_dim_sum USING(pid)
                                                                LEFT JOIN (SELECT pid,uid FROM post_score WHERE uid='8')post_rated USING(pid)
								LEFT JOIN (SELECT mark_post.pid,mark_post.uid, mark_post.mpid FROM mark_post WHERE mark_post.pid='$pid' AND mark_post.uid='$uid')mp
                                                                ON (post.pid=mp.pid)
								WHERE post.pid='$pid'
								GROUP BY pid");
		$posts = array();
		$mg_flag = get_magic_quotes_gpc();
		while ($post = $result->fetch_object()) {
			if( $mg_flag ) {
				$post->body  = stripslashes( $post->body  );
				$post->title  = stripslashes( $post->title );
			}
			$posts[] = $post;
			// update the recent post table
			if( !DB::query("INSERT INTO recent_post (pid, uid) VALUES ('$pid','$uid')") ) {
				DB::query("UPDATE recent_post SET last_visit = CURRENT_TIMESTAMP WHERE pid = '$pid' AND uid = '$uid'");
			}
		}
		return $posts;
	}

    public static function get_post_galaxies($pid) {
		$pid = DB::esc($pid);
		$query;
		if( isset($_SESSION['user']) ) {
			$uid = $_SESSION['user']['uid'];
			$query = "SELECT gid, title, galaxy.uid, description, icon, galaxy.ts, gpid, pid, galaxy_post.ts, joined, galaxy_lit, galaxy_dim, galaxy_rated.uid IS NOT NULL AS rated
						FROM galaxy
						JOIN galaxy_post
						USING ( gid ) 
						LEFT JOIN (
							SELECT uid IS NOT NULL AS joined, gid
							FROM chatuser
							WHERE chatuser.uid = '$uid' AND chatuser.toshow = 'true'
						)users
						USING ( gid )
						LEFT JOIN (
							SELECT COUNT(score) AS galaxy_lit, gid 
							FROM galaxy_score WHERE score=1 GROUP BY gid
						)lit_scores
						USING(gid)
						LEFT JOIN (
							SELECT COUNT(score) AS galaxy_dim, gid 
							FROM galaxy_score WHERE score=-1 GROUP BY gid
						)dim_scores
						USING (gid)
						LEFT JOIN (
							SELECT gid, uid FROM galaxy_score WHERE uid='$uid'
							)galaxy_rated
						USING ( gid ) WHERE galaxy_post.pid = '$pid'";
		} else {
			$query = "SELECT * FROM galaxy JOIN galaxy_post USING( gid ) WHERE galaxy_post.pid= $pid";
		}
		$result = DB::query($query);
		$galaxies = array();
		while ($galaxy = $result->fetch_object()) {
			$galaxies[] = $galaxy;
		}
		return $galaxies;
	}
	public static function posts_for_user_profile( $uid ) {
		$uid   = DB::esc($info);
		$query = "SELECT pid, title, ts FROM post WHERE uid = '$uid' ORDER BY ts DESC";
		$result = DB::query($query);
		$posts = array();
		while ($post = $result->fetch_object()) {
			$posts[] = $post;
		}
		return $posts;
	}
	
	public static function update_post( $post ) {
		if (!isset($_SESSION['user']))
			return 0; // user is not login
		$title = $post['title'];
		$nids  = explode(',', DB::esc($post['nids']));
		$body  = nl2br($post['body']);
		$uid   = $_SESSION['user']['uid']; // only enable posting by current user
		$pid   = $post['pid'];

		$query = "UPDATE post SET title = '$title', body = '$body', uid = '$uid'
					WHERE pid = '$pid' AND uid = '$uid'";
		if (DB::query($query)) { // editing is successful
			DB::query("DELETE FROM post_nebula WHERE uid = '$uid' AND pid = '$pid'");
			foreach ($nids as $nid) {
				DB::query("INSERT INTO post_nebula (uid, nid, pid) VALUES ('$uid', '$nid', '$pid')");
			}
			return $pid; // posting is done
		} else {
			return -3; // something is wrong
		}	
	}
	
	public static function delete_post( $pid ) {
		$pid = (int)$pid;
		$uid = $_SESSION['user']['uid'];
		if( DB::query("DELETE FROM post WHERE uid = '$uid' AND pid = '$pid'") ) {
			return 1;
		} else {
			return 0; // error ? either the post does not exist any longer, or the user is not the creator of the post
		}
	}
	
	public static function mark_post( $pid ) {
		$pid = (int)$pid;
		$uid = $_SESSION['user']['uid'];
		return DB::query( "INSERT INTO mark_post (uid, pid) VALUES ('$uid', '$pid')" );
	}

	public static function unmark_post( $pid ) {
		$pid = (int)$pid;
		$uid = $_SESSION['user']['uid'];
		return DB::query( "DELETE FROM mark_post WHERE uid = '$uid' AND pid = '$pid'" );
	}
	
	public static function recent_posts($num, $uid) {
		$uid = DB::esc($uid);
		$query = "
			SELECT pid, title, body, post.ts AS ts, post.uid AS post_uid,default_nid, last_visit, username, avatar FROM post
			JOIN recent_post USING(pid)
            JOIN user ON(user.uid = post.uid)
			WHERE recent_post.uid = '$uid' 
			ORDER BY last_visit DESC
			LIMIT 0, $num";
		$result = DB::query($query);
		$posts  = array();
		$mg_flag = get_magic_quotes_gpc();
		while ($post = $result->fetch_object()) {
			if( $mg_flag ) {
				$post->title  = stripslashes( $post->title );
				$post->body  = stripslashes( $post->body );
			}
			$posts[] = $post;
		}
		return $posts;
	}
	
}
?>
